# SQLite3 : SQLite3 SQL database

This module is the encapsulation of the SQLite3 database. SQLite3 is a standard transactional SQL database for embedded system. This chapter only describes the interfaces related to the SQLite3 database. For the SQLite3 database description, please refer to the related documentation.

User can use the following code to import the Sqlite3 module.

var Sqlite3 = require('sqlite3');

# Data type conversion

The SQLite3 database has the following data types:

• NULL null.
• INTEGER Signed integer.
• REAL Double precision floating point number.
• TEXT Text type can support UTF-8, UTF-16 codec.
• BLOB Binary data type.

The conversion of these data types to JavaScript types is as follows:

Variable binding and query results are both with this conversion criterion.

# Sqlite3 Error Code

SQLite3 database operation may fail, the possible error codes include:

You can use Sqlite3.error() to convert the error code to error string.

# Support

The following shows Sqlite3 module APIs available for each permissions.

# Sqlite3 Class

# new Sqlite3(fileName[, flags])

• fileName {String} Database file name.
• flags {String} Open flags. default: 'c+'.
• Returns: {Object} Database object.

Open a database using the specified method. flags can be:

When fileName is ':memory:', it means creating an anonymous in-memory database.

# Example

var db = new Sqlite3(':memory:');
var db = new Sqlite3('./dat.db3');

# Sqlite3.version()

• Returns: {String} Sqlite3 library version string.

Get Sqlite3 library version.

# Sqlite3.open(fileName[, flags])

• fileName {String} Database file name.
• flags {String} Open flags. default: 'c+'.
• Returns: {Object} Database object.

Same as new Sqlite3(), but does not throw an exception, returning undefined means opening failed.

# Sqlite3.error(errCode)

• errCode {Integer} Sqlite3 error code.
• Returns: {String} Sqlite3 error string.

Convert the error code to error string.

# Sqlite3 Object

# db.lastRowid

• {Integer}

Rowid at last insert, which is not recommended without special circumstances.

# db.close()

Close database object.

# db.backup(destFile)

• destFile {String} Destination file.
• Returns: {Integer} Sqlite3 error code, Sqlite3.OK or Sqlite3.DONE is success.

Back up the current database to the specified file.

# Example

var db = new Sqlite3('./dat.db3');

db.backup('./dat.db3.bk');

# db.begin()

• Returns: {Integer} Sqlite3 error code, Sqlite3.OK or Sqlite3.DONE is success.

Open a transaction. Same as db.run('BEGIN;').

# Example

db.begin();
db.run('INSERT INTO user VALUES("Jack", 23);');
db.run('INSERT INTO user VALUES("Rose", 21);');
db.commit();

# db.commit()

• Returns: {Integer} Sqlite3 error code, Sqlite3.OK or Sqlite3.DONE is success.

Commit a transaction. Same as db.run('COMMIT;').

# db.rollback()

• Returns: {Integer} Sqlite3 error code, Sqlite3.OK or Sqlite3.DONE is success.

Rollback a transaction. Same as db.run('ROLLBACK;').

# Example

db.begin();
db.run('INSERT INTO user VALUES("Jack", 23);');
db.run('INSERT INTO user VALUES("Rose", 21);');
db.rollback();

# db.run(sql[, ...bind[, query[, arg]]])

• sql {String} SQL statement.
• ...bind {Any} Variables bound according to '?' in the SQL statement. default: no variable binding.
• query {Function} If it is a query statement, each record queried will call back this function. default: no callback.
  • arg {Any} Optional callback argument, if no argument present, on this parameter.
  • row {Object} Query result object.
• arg {Any} Callback argument. default: undefined.
• Returns: {Integer} Sqlite3 error code, Sqlite3.OK is success.

Run an SQL statement.

# Example

var db = Sqlite3.open(':memory:');

db.run('CREATE TABLE user(name text, age int);');

db.run('INSERT INTO user VALUES("Jack", 23);');
db.run('INSERT INTO user VALUES("Rose", 21);');

function queryCallback(row) {
  console.log('name', row.name, 'age', row.age);
}

db.run('SELECT * FROM user;', queryCallback);

Can be bound using JavaScript variables:

# Example

var db = Sqlite3.open(':memory:');

db.run('CREATE TABLE user(name text, age int);');

db.run('INSERT INTO user VALUES(?, ?);', 'Jack', 23);
db.run('INSERT INTO user VALUES(?, ?);', 'Rose', 21);

function queryCallback(row) {
  console.log('name', row.name, 'age', row.age);
}

db.run('SELECT * FROM user;', queryCallback);

# db[Symbol.iterator](sql[, ...bind])

• sql {String} SQL statement.
• ...bind {Any} Variables bound according to '?' in the SQL statement. default: no variable binding.
• Returns: {Iterator} Iterator object.

The [Symbol.iterator] method returns a new Iterator object that contains the values for each element in this database.

# Example

var db = Sqlite3.open(':memory:');

db.run('CREATE TABLE user(name text, age int);');

db.run('INSERT INTO user VALUES(?, ?);', 'Jack', 23);
db.run('INSERT INTO user VALUES(?, ?);', 'Rose', 21);

var it = db[Symbol.iterator]('SELECT * FROM user;');

for (var row of it) {
  console.log('name', row.name, 'age', row.age);
}

# Example

for (var row of it) {
  if (/* Some case */) {
    it.finalize();
    break;
  }
}

# db.prepare(sql[, ...bind[, query[, arg]]])

• sql {String} SQL statement.
• ...bind {Any} Variables bound according to '?' in the SQL statement. default: no variable binding.
• query {Function} If it is a query statement, each record queried will call back this function. default: no callback.
  • arg {Any} Optional callback argument, if no argument present, on this parameter.
  • row {Object} Query result object.
• arg {Any} Callback argument. default: undefined.
• Returns: {Object} Statement prepair object.

Prepare an SQL statement and return a statement prepair object.

# Example

var stmt = db.prepare('SELECT * FROM user;', queryCallback);

# Stmt Object

# stmt.step([...bind[, query[, arg]]])

• ...bind {Any} Variables bound according to '?' in the SQL statement. default: no variable binding.
• query {Function} If it is a query statement, each record queried will call back this function. default: no callback.
  • arg {Any} Optional callback argument, if no argument present, on this parameter.
  • row {Object} Query result object.
• arg {Any} Callback argument. default: undefined.
• Returns: {Integer} Single step result.

The single step result can be:

• Sqlite3.OK Successful.
• Sqlite3.ROW Query a row of results.
• Sqlite3.DONE Finished executing.
• Other values represent errors.

Step through a SQL statement. Allows rebinding of variables in SQL statements.

# Example

var db = Sqlite3.open(':memory:');

db.run('CREATE TABLE user(name text, age int);');

db.run('INSERT INTO user VALUES(?, ?);', 'Jack', 23);
db.run('INSERT INTO user VALUES(?, ?);', 'Rose', 21);

var stmt = db.prepare('SELECT * FROM user;', queryCallback);

do {
  var ret = stmt.step((row) => {
    console.log('name', row.name, 'age', row.age);
  });
} while (ret === Sqlite3.ROW);

stmt.finalize();

Rebinding:

# Example

var db = Sqlite3.open(':memory:');

db.run('CREATE TABLE user(name text, age int);');

var stmt = db.prepare('INSERT INTO user VALUES(?, ?);');

stmt.step('Jack', 23);
stmt.step('Rose', 21);

stmt.finalize();

# stmt.reset()

Reset a SQL statement, clear all variable bindings.

# stmt.finalize()

Finalize a SQL statement.